package com.css.fxfzypg.modules.baseData.repository.impl;

import com.alibaba.fastjson.JSONObject;
import com.css.fxfzypg.constants.FxfzConstants;
import com.css.fxfzypg.constants.YNEnum;
import com.css.fxfzypg.modules.baseData.entity.HosPeoEntity;
import com.css.fxfzypg.modules.baseData.entity.VillageTownHosPeoEntity;
import com.css.fxfzypg.modules.baseData.queryParams.VillageTownHosPeoQueryParams;
import com.css.fxfzypg.modules.baseData.repository.VillageTownHosPeoRepository;
import com.css.fxfzypg.util.CreateGson;
import com.css.fxfzypg.util.PlatformDateUtils;
import com.css.fxfzypg.util.PlatformObjectUtils;
import com.css.fxfzypg.util.PlatformSessionUtils;
import com.css.fxfzypg.web.PlatformSessionContext;
import com.css.fxfzypg.zcpt.sys.entity.SUser;
import com.css.fxfzypg.zcpt.sys.service.SUserService;
import com.css.fxfzypg.zcpt.sys.service.SysRoleService;
import org.springframework.jdbc.core.BatchPreparedStatementSetter;
import org.springframework.jdbc.core.BeanPropertyRowMapper;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.BeanPropertySqlParameterSource;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.jdbc.core.namedparam.SqlParameterSource;
import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.jdbc.support.KeyHolder;
import org.springframework.stereotype.Repository;

import javax.annotation.Resource;
import java.sql.PreparedStatement;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.List;
import java.util.Map;

/**
 * @author y1fan
 * @create 2022-08-23 12:50
 */
@Repository
public class VillageTownHosPeoRepositoryImpl implements VillageTownHosPeoRepository {

    @Resource
    private JdbcTemplate jdbcTemplate;
    @Resource
    private NamedParameterJdbcTemplate namedParameterJdbcTemplate;

    @Resource
    SysRoleService sysRoleService;

    @Resource
    SUserService sUserService;

    @Override
    public int saveHosPeo(VillageTownHosPeoEntity hosPeoEntity) {
        String sql = "insert into data_village_town_hos_peo(dhp_id, province, city, prefecture,village_town, dhp_county_area, dhp_total_pop, " +
                "dhp_town_pop, dhp_village_pop, dhp_young_ratio, dhp_middle_ratio, dhp_old_tatio, dhp_house_tatio, " +
                "dhp_town_high_tatio, dhp_town_steel_tatio, dhp_town_beton_tatio, dhp_town_brick_tatio, " +
                "dhp_town_masonry_tatio, dhp_town_soil_tatio, dhp_town_wood_tatio, dhp_town_stone_tatio, " +
                "dhp_town_other_tatio, dhp_town_seven_before_tatio, dhp_town_seven_tatio, dhp_town_eight_tatio, " +
                "dhp_town_nine_tatio, dhp_town_zero_tatio, dhp_town_ten_tatio, dhp_town_indoor_property, " +
                "dhp_village_high_tatio, dhp_village_steel_tatio, dhp_village_beton_tatio, dhp_village_brick_tatio, " +
                "dhp_village_masonry_tatio, dhp_village_soil_tatio, dhp_village_wood_tatio, dhp_village_stone_tatio, " +
                "dhp_village_other_tatio, dhp_village1980_before, dhp_village8190_tatio, " +
                "dhp_village9100_tatio, dhp_village0110_tatio, dhp_village1115_tatio, dhp_village1620_tatio, " +
                "dhp_village_indoor_property, create_user, create_time, is_delete, province_id, city_id, prefecture_id,province_code," +
                "city_code,prefecture_code,dhp_town_tatio,dhp_town_sixteen_tatio,dhp_town_twenty_one_tatio,dhp_village21_after_tatio) " +
                "values(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?," +
                "?,?,?,?,?,?,?,?,?,?,?,?)";
        int i = jdbcTemplate.update(sql, hosPeoEntity.getDhpId(), hosPeoEntity.getProvince(), hosPeoEntity.getCity(),
                hosPeoEntity.getPrefecture(),hosPeoEntity.getVillageTown() ,hosPeoEntity.getDhpCountyArea(), hosPeoEntity.getDhpTotalPop(),
                hosPeoEntity.getDhpTownPop(), hosPeoEntity.getDhpVillagePop(), hosPeoEntity.getDhpYoungRatio(),
                hosPeoEntity.getDhpMiddleRatio(), hosPeoEntity.getDhpOldTatio(), hosPeoEntity.getDhpHouseTatio(),
                hosPeoEntity.getDhpTownHighTatio(), hosPeoEntity.getDhpTownSteelTatio(),
                hosPeoEntity.getDhpTownBetonTatio(), hosPeoEntity.getDhpTownBrickTatio(),
                hosPeoEntity.getDhpTownMasonryTatio(), hosPeoEntity.getDhpTownSoilTatio(),
                hosPeoEntity.getDhpTownWoodTatio(), hosPeoEntity.getDhpTownStoneTatio(),
                hosPeoEntity.getDhpTownOtherTatio(), hosPeoEntity.getDhpTownSevenBeforeTatio(),
                hosPeoEntity.getDhpTownSevenTatio(), hosPeoEntity.getDhpTownEightTatio(),
                hosPeoEntity.getDhpTownNineTatio(), hosPeoEntity.getDhpTownZeroTatio(),
                hosPeoEntity.getDhpTownTenTatio(), hosPeoEntity.getDhpTownIndoorProperty(),
                hosPeoEntity.getDhpVillageHighTatio(), hosPeoEntity.getDhpVillageSteelTatio(),
                hosPeoEntity.getDhpVillageBetonTatio(), hosPeoEntity.getDhpVillageBrickTatio(),
                hosPeoEntity.getDhpVillageMasonryTatio(), hosPeoEntity.getDhpVillageSoilTatio(),
                hosPeoEntity.getDhpVillageWoodTatio(), hosPeoEntity.getDhpVillageStoneTatio(),
                hosPeoEntity.getDhpVillageOtherTatio(), hosPeoEntity.getDhpVillage1980Before(),
                hosPeoEntity.getDhpVillage8190Tatio(), hosPeoEntity.getDhpVillage9100Tatio(),
                hosPeoEntity.getDhpVillage0110Tatio(), hosPeoEntity.getDhpVillage1115Tatio(),
                hosPeoEntity.getDhpVillage1620Tatio(), hosPeoEntity.getDhpVillageIndoorProperty(),
                hosPeoEntity.getCreateUser(), hosPeoEntity.getCreateTime(), hosPeoEntity.getIsDelete(),
                hosPeoEntity.getProvinceId(), hosPeoEntity.getCityId(), hosPeoEntity.getPrefectureId(),
                hosPeoEntity.getProvinceCode(), hosPeoEntity.getCityCode(), hosPeoEntity.getPrefectureCode(),
                hosPeoEntity.getDhpTownTatio(), hosPeoEntity.getDhpTownSixteenTatio()
                , hosPeoEntity.getDhpTownTwentyOneTatio()
                , hosPeoEntity.getDhpVillage21AfterTatio()
        );
        return i;
    }

    @Override
    public VillageTownHosPeoEntity fingHosPeoById(String id) {
        String sql = "select * from data_village_town_hos_peo where dhp_id ='" + id + "' and is_delete='" + YNEnum.N.toString() + "'";
        List<VillageTownHosPeoEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));
        return list.size()>0?list.get(0):new VillageTownHosPeoEntity();
    }

    @Override
    public VillageTownHosPeoEntity fingHosPeoBydivision(String provinceId, String cityId, String prefectureId,String villageTown) {
        String sql ="select * from data_village_town_hos_peo where province_id ='" + provinceId + "' and city_id ='" + cityId + "'" +
                        " and prefecture_id ='" + prefectureId + "' and village_town ='"+villageTown+"' and is_delete='" + YNEnum.N.toString() + "'";
        List<VillageTownHosPeoEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));
        return list.size()>0?list.get(0):null;

    }

    @Override
    public int updateHosPeo(VillageTownHosPeoEntity hosPeoEntity) {
        String sql = "update data_village_town_hos_peo set dhp_county_area = :dhpCountyArea, dhp_total_pop = :dhpTotalPop, " +
                "dhp_town_pop = :dhpTownPop, dhp_village_pop = :dhpVillagePop, dhp_young_ratio = :dhpYoungRatio, " +
                "dhp_middle_ratio = :dhpMiddleRatio, dhp_old_tatio = :dhpOldTatio, dhp_house_tatio = :dhpHouseTatio, " +
                "dhp_town_high_tatio = :dhpTownHighTatio, dhp_town_steel_tatio = :dhpTownSteelTatio, " +
                "dhp_town_beton_tatio = :dhpTownBetonTatio, dhp_town_brick_tatio = :dhpTownBrickTatio, " +
                "dhp_town_masonry_tatio = :dhpTownMasonryTatio, dhp_town_soil_tatio = :dhpTownSoilTatio, " +
                "dhp_town_wood_tatio = :dhpTownWoodTatio, dhp_town_stone_tatio = :dhpTownStoneTatio, " +
                "dhp_town_other_tatio = :dhpTownOtherTatio, dhp_town_seven_before_tatio = :dhpTownSevenBeforeTatio, " +
                "dhp_town_seven_tatio = :dhpTownSevenTatio, dhp_town_eight_tatio = :dhpTownEightTatio , " +
                "dhp_town_nine_tatio = :dhpTownNineTatio, dhp_town_zero_tatio = :dhpTownZeroTatio, " +
                "dhp_town_ten_tatio = :dhpTownTenTatio, dhp_town_indoor_property = :dhpTownIndoorProperty, " +
                "dhp_village_high_tatio = :dhpVillageHighTatio, dhp_village_steel_tatio = :dhpVillageSteelTatio, " +
                "dhp_village_beton_tatio = :dhpVillageBetonTatio, dhp_village_brick_tatio = :dhpVillageBrickTatio, " +
                "dhp_village_masonry_tatio = :dhpVillageMasonryTatio, dhp_village_soil_tatio = :dhpVillageSoilTatio, " +
                "dhp_village_wood_tatio = :dhpVillageWoodTatio, dhp_village_stone_tatio = :dhpVillageStoneTatio, " +
                "dhp_village_other_tatio = :dhpVillageOtherTatio, dhp_village1980_before = :dhpVillage1980Before, " +
                "dhp_village8190_tatio = :dhpVillage8190Tatio, update_user = :updateUser, " +
                "dhp_village9100_tatio = :dhpVillage9100Tatio, dhp_village0110_tatio = :dhpVillage0110Tatio, " +
                "dhp_village1115_tatio = :dhpVillage1115Tatio, dhp_village1620_tatio = :dhpVillage1620Tatio, " +
                "dhp_village_indoor_property = :dhpVillageIndoorProperty, update_time = :updateTime, " +
                "dhp_town_tatio=:dhpTownTatio,dhp_town_sixteen_tatio=:dhpTownSixteenTatio,dhp_town_twenty_one_tatio=:dhpTownTwentyOneTatio,dhp_village21_after_tatio=:dhpVillage21AfterTatio  " +
                "where dhp_id = :dhpId and is_delete='" + YNEnum.N.toString() + "'";
        SqlParameterSource source = new BeanPropertySqlParameterSource(hosPeoEntity);
        //拿到新插入的主键值（i）
        KeyHolder keyHolder = new GeneratedKeyHolder();
        int i = namedParameterJdbcTemplate.update(sql, source, keyHolder);
        return i;
    }

    @Override
    public JSONObject getAllHosPerByParams(VillageTownHosPeoQueryParams queryParams, int curPage, int pageSize) {
        StringBuilder str=new StringBuilder("");
        StringBuilder sql = new StringBuilder(" select  u.* from data_village_town_hos_peo u ");
        StringBuilder whereSql = new StringBuilder(" where u.is_delete = '" + YNEnum.N.toString() + "'");
        if (!PlatformObjectUtils.isEmpty(queryParams.getProvinceId())) {
            whereSql.append(" and u.province_id ='" + queryParams.getProvinceId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getCityId())) {
            whereSql.append(" and u.city_id  ='" + queryParams.getCityId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getPrefectureId())) {
            whereSql.append(" and u.prefecture_id ='" + queryParams.getPrefectureId() + "'");
        }
        if (!PlatformObjectUtils.isEmpty(queryParams.getVillageTown())) {
            whereSql.append(" and u.village_town like '%"+ queryParams.getVillageTown() +"%'");
        }
        String userId = PlatformSessionContext.getUserID();
        String user = sUserService.getSUser(userId);
        SUser sUser = CreateGson.createGson().fromJson(user, SUser.class);
        String role = sysRoleService.getRoleByUserId(userId);
        List<Map> roleMaps = JSONObject.parseArray(role, Map.class);
        for(Map roleMap:roleMaps){
            str.append(roleMap.get("roleCode")+",");
        }
        String roles = str.toString();
        if(roles.contains(FxfzConstants.YPG_COUN_GL_ROLE) || roles.contains(FxfzConstants.YPG_COUN_YW_ROLE)){
            //国家级管理员和国家级业务人员可以全看全部
        }else if(roles.contains(FxfzConstants.YPG_PRI_GL_ROLE) || roles.contains(FxfzConstants.YPG_PRI_YW_ROLE)){
            //省级管理员和省级业务人员可以全看自己
            whereSql.append(" and u.province_id = '"+sUser.getProvinceId()+"'");
        }
        StringBuilder orderSql = new StringBuilder();
        if ("0".equals(queryParams.getOption())){
            orderSql.append(" order by u.province_code,u.city_code,u.prefecture_code");
        }
        if ("1".equals(queryParams.getOption())){
            orderSql.append(" order by COALESCE (u.update_time,u.create_time) desc");
        }
//        StringBuilder orderSql = new StringBuilder(" order by u.province_code,u.city_code,u.prefecture_code");
        sql.append(whereSql).append(orderSql).toString();
        int firstIndex = (curPage - 1) * pageSize;
        int lastIndex = curPage * pageSize;
        StringBuilder limitSql = new StringBuilder(" limit " + lastIndex + " offset " + firstIndex);
        Integer total = jdbcTemplate.query(sql.toString(), new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class)).size();
        List<VillageTownHosPeoEntity> list = jdbcTemplate.query(sql.append(limitSql).toString(),
                new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));
        JSONObject jsonObject = new JSONObject();
        jsonObject.put("rows", list);
        jsonObject.put("total", total);
        return jsonObject;
    }

    @Override
    public void deleteHosPeo(String id, String userId, Timestamp currentTimestamp) {
        String sql = "update data_village_town_hos_peo set is_delete = '" + YNEnum.Y.toString() +
                "' ,update_time ='" + currentTimestamp + "', update_user='" + userId + "'" +
                " where dhp_id = '" + id + "' and is_delete = '" + YNEnum.N.toString()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public List<VillageTownHosPeoEntity> exportExcel() {
        return jdbcTemplate.query("select  u.* from data_village_town_hos_peo u where u.is_delete='" + YNEnum.Y.toString() + "'",
                new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));

    }

    @Override
    public void importExcel(List<HosPeoEntity> hosPeoEntities) {
        jdbcTemplate.batchUpdate("insert into data_village_town_hos_peo(" +
                "dhp_id,dhp_province,dhp_city,dhp_prefecture,village_town,dhp_county_area," +
                "dhp_total_pop,dhp_town_pop,dhp_village_pop,dhp_young_ratio,dhp_middle_ratio,dhp_old_tatio,dhp_house_tatio," +
                "dhp_town_high_tatio,dhp_town_steel_tatio," +
                "dhp_town_beton_tatio,dhp_town_brick_tatio,dhp_town_masonry_tatio,dhp_town_soil_tatio," +
                "dhp_town_wood_tatio,dhp_town_stone_tatio,dhp_town_other_tatio," +
                "dhp_town_seven_before_tatio,dhp_town_seven_tatio,dhp_town_eight_tatio," +
                "dhp_town_nine_tatio,dhp_town_zero_tatio,dhp_town_ten_tatio," +
                "dhp_town_indoor_property," +
                "dhp_village_high_tatio,dhp_village_steel_tatio," +
                "dhp_village_beton_tatio,dhp_village_brick_tatio,dhp_village_masonry_tatio,dhp_village_soil_tatio," +
                "dhp_village_wood_tatio,dhp_village_stone_tatio,dhp_village_other_tatio," +
                "dhp_village_seven_before_tatio,dhp_village_seven_tatio,dhp_village_eight_tatio," +
                "dhp_village_nine_tatio,dhp_village_zero_tatio,dhp_village_ten_tatio," +
                "dhp_village_indoor_property," +
                "dhp_createtime,dhp_isdelete) " +
                "values(?,?,?,?,?,?,?,?,?,?," +
                "       ?,?,?,?,?,?,?,?,?,?," +
                "       ?,?,?,?,?,?,?,?,?,?," +
                "       ?,?,?,?,?,?,?,?,?,?," +
                "       ?,?,?,?,?,?,?)", new BatchPreparedStatementSetter() {
            @Override
            public void setValues(PreparedStatement ps, int i) throws SQLException {
                // ps.setString(1,hosPeoEntities.get(i).getDhpId());
                // ps.setString(2,hosPeoEntities.get(i).getProvince());
                // ps.setString(3,hosPeoEntities.get(i).getCity());
                // ps.setString(4,hosPeoEntities.get(i).getPrefecture());
                // ps.setFloat(5,hosPeoEntities.get(i).getDhpCountyArea());
                // ps.setFloat(6,hosPeoEntities.get(i).getDhpTotalPop());
                // ps.setFloat(7,hosPeoEntities.get(i).getDhpTownPop());
                // ps.setFloat(8,hosPeoEntities.get(i).getDhpVillagePop());
                // ps.setFloat(9,hosPeoEntities.get(i).getDhpYoungRatio());
                // ps.setFloat(10,hosPeoEntities.get(i).getDhpMiddleRatio());
                // ps.setFloat(11,hosPeoEntities.get(i).getDhpOldTatio());
                //
                // ps.setFloat(12,hosPeoEntities.get(i).getDhpHouseTatio());
                //
                // ps.setFloat(13,hosPeoEntities.get(i).getDhpTownHighTatio());
                // ps.setFloat(14,hosPeoEntities.get(i).getDhpTownSteelTatio());
                // ps.setFloat(15,hosPeoEntities.get(i).getDhpTownBetonTatio());
                // ps.setFloat(16,hosPeoEntities.get(i).getDhpTownBrickTatio());
                // ps.setFloat(17,hosPeoEntities.get(i).getDhpTownMasonryTatio());
                // ps.setFloat(18,hosPeoEntities.get(i).getDhpTownSoilTatio());
                // ps.setFloat(19,hosPeoEntities.get(i).getDhpTownWoodTatio());
                // ps.setFloat(20,hosPeoEntities.get(i).getDhpTownStoneTatio());
                // ps.setFloat(21,hosPeoEntities.get(i).getDhpTownOtherTatio());
                // ps.setFloat(22,hosPeoEntities.get(i).getDhpTownSevenBeforeTatio());
                // ps.setFloat(23,hosPeoEntities.get(i).getDhpTownSevenTatio());
                // ps.setFloat(24,hosPeoEntities.get(i).getDhpTownEightTatio());
                // ps.setFloat(25,hosPeoEntities.get(i).getDhpTownNineTatio());
                // ps.setFloat(26,hosPeoEntities.get(i).getDhpTownZeroTatio());
                // ps.setFloat(27,hosPeoEntities.get(i).getDhpTownTenTatio());
                // ps.setFloat(28,hosPeoEntities.get(i).getDhpTownIndoorProperty());
                // ps.setFloat(29,hosPeoEntities.get(i).getDhpVillageHighTatio());
                // ps.setFloat(30,hosPeoEntities.get(i).getDhpVillageSteelTatio());
                // ps.setFloat(31,hosPeoEntities.get(i).getDhpVillageBetonTatio());
                // ps.setFloat(32,hosPeoEntities.get(i).getDhpVillageBrickTatio());
                // ps.setFloat(33,hosPeoEntities.get(i).getDhpVillageMasonryTatio());
                // ps.setFloat(34,hosPeoEntities.get(i).getDhpVillageSoilTatio());
                // ps.setFloat(35,hosPeoEntities.get(i).getDhpVillageWoodTatio());
                // ps.setFloat(36,hosPeoEntities.get(i).getDhpVillageStoneTatio());
                // ps.setFloat(37,hosPeoEntities.get(i).getDhpVillageOtherTatio());
                // ps.setFloat(38,hosPeoEntities.get(i).getDhpVillageSevenBeforeTatio());
                // ps.setFloat(39,hosPeoEntities.get(i).getDhpVillageSevenTatio());
                // ps.setFloat(40,hosPeoEntities.get(i).getDhpVillageEightTatio());
                // ps.setFloat(41,hosPeoEntities.get(i).getDhpVillageNineTatio());
                // ps.setFloat(42,hosPeoEntities.get(i).getDhpVillageZeroTatio());
                // ps.setFloat(43,hosPeoEntities.get(i).getDhpVillageTenTatio());
                // ps.setFloat(44,hosPeoEntities.get(i).getDhpVillageIndoorProperty());
                //
                // java.sql.Date createDate = new java.sql.Date(hosPeoEntities.get(i).getCreateTime().getTime());
                // ps.setDate(45,  createDate);
                // ps.setString(46,hosPeoEntities.get(i).getIsDelete());
            }

            @Override
            public int getBatchSize() {
                return hosPeoEntities.size();
            }
        });
    }

    @Override
    public VillageTownHosPeoEntity findHosPeo(String province, String city, String prefecture, String street) {
        String sql ="select * from data_village_town_hos_peo where province ='" + province + "' and city ='" + city + "'" +
                " and prefecture ='" + prefecture + "' and village_town ='"+street+"' and is_delete='" + YNEnum.N.toString() + "'";
        List<VillageTownHosPeoEntity> list = jdbcTemplate.query(sql, new BeanPropertyRowMapper<>(VillageTownHosPeoEntity.class));
        return list.size()>0?list.get(0):null;
    }

    @Override
    public void delete(VillageTownHosPeoEntity entity) {
        String userId = PlatformSessionUtils.getUserId();
        Timestamp currentTimestamp = PlatformDateUtils.getCurrentTimestamp();
        String sql = "update data_village_town_hos_peo set is_delete = '" + YNEnum.Y.toString() +
                "' ,update_time ='" + currentTimestamp + "', update_user='" + userId + "'" +
                " where province = '" + entity.getProvince() + "' and city = '" + entity.getCity()+
                "' and prefecture ='"+entity.getPrefecture()+"' and village_town='"+entity.getVillageTown()+"'";
        jdbcTemplate.update(sql);
    }

    @Override
    public Integer countByProvinceAndYear(String provinceName, String taskYear) {
        return jdbcTemplate.queryForObject("SELECT count(*) from data_village_town_hos_peo where province = ? and (TO_CHAR(update_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ? or TO_CHAR(create_time, 'yyyy-MM-dd hh24:mi:ss') LIKE ?) and is_delete ='0' ", Integer.class, provinceName, taskYear + "%", taskYear + "%");
    }
}
